package com.film.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.film.dao.ReviewDao;
import com.film.domain.Review;
import com.film.domain.ReviewAndFilm;
import com.film.rowMapper.ReviewAndFilmRowmapper;
import com.film.rowMapper.ReviewRowMapper;
import com.film.util.DBUtil;

public class ReviewDaoImpl implements ReviewDao{
	private JdbcTemplate temp = new JdbcTemplate(DBUtil.getDataScorse());
	@Override
	public int insertReview(Review review) {
		String sql = "insert into review values(default,?,?,?,?,?,?)";
		return temp.update(sql,review.getFilm().getFilmId(),review.getUser().getuId(),review.getRevTime(),review.getRevContent(),review.getRevScore(),review.getRevPraise());
	}

	@Override
	public List<Review> selectAllReview(int filmId) {
		String sql = "select * from review t1,user t2,film t3 where t1.u_id = t2.u_id and t1.film_id = t3.film_id and t3.film_id = ?";
		return temp.query(sql, new ReviewRowMapper(),filmId);
	}

	@Override
	public Review selectReviewInfo(int filmId, int uId) {
		String sql = "select * from review t1,user t2,film t3 where t1.u_id = t2.u_id and t1.film_id = t3.film_id and t3.film_id = ? and t2.u_id = ?";
		List<Review> review = temp.query(sql, new ReviewRowMapper(),filmId,uId);
		System.out.println(review.size());
		if(review.size()!= 0) {
			System.out.println("进来了");
			return review.get(0);
		}else {
			return null;
		}
		
	}

	@Override
	public int updateReview(Review review) {
		String sql = "update review set rev_time = ? , rev_content = ? , rev_score = ? where film_id = ? and u_id = ?";
		return temp.update(sql,review.getRevTime(),review.getRevContent(),review.getRevScore(),review.getFilm().getFilmId(),review.getUser().getuId());
	}

	@Override
	public int updateReviewPraise(int praise, int revId) {
		String sql = "update review set rev_praise = ? where rev_id = ?";
		return temp.update(sql,praise,revId);
	}

	@Override
	public ReviewAndFilm selectFilmReviewScore(int film_id) {
		String sql = "select sum(rev_score) as score,count(1) as count from review where film_id = ? GROUP BY film_id";
		List<ReviewAndFilm> res = temp.query(sql, new ReviewAndFilmRowmapper(),film_id);
		if(res.size()!=0) {
			return res.get(0);
		}else {
			return null ;
		}
		
	}

	@Override
	public List<Review> selectReview() {
		String sql="SELECT DISTINCT * FROM user t1,film t2,review t3 WHERE t1.u_id=t3.u_id and t2.film_id=t3.film_id ORDER BY t3.rev_id";
		return temp.query(sql,new ReviewRowMapper());
	}

	@Override
	public int deleteReviewById(int id) {
		String sql="DELETE FROM review WHERE rev_id=?";
		return temp.update(sql,id);
	}

	@Override
	public List<Review> selectlimit( int a,int b) {
		String sql="SELECT DISTINCT * FROM user t1,film t2,review t3 WHERE t1.u_id=t3.u_id and t2.film_id=t3.film_id ORDER BY t3.rev_id LIMIT ?,?";
		return temp.query(sql,new ReviewRowMapper(),a,b);
	}

	@Override
	public int deleteReviewPraise(int reviewId) {
		String sql = "delete from reviewpraise where rev_id = ?";
		return temp.update(sql,reviewId);
	}

	@Override
	public int selectFilmIdByRevId(int revId) {
		String sql = "select film_id from review where rev_id = ?";
		return temp.queryForObject(sql, int.class,revId);
	}
}
